ALTER VIEW [dbo].[z_png_webform_submission_VM]
AS
SELECT B1.sid, B1.nid, B1.is_draft, B1.submitted, B1.status, B1.cancelled_time, B2.country, B2.employee_last_name, B2.employee_first_name, B2.relocation_effective_date, B2.relocation_type
FROM (
    SELECT A1.sid, A1.nid, A1.is_draft, A1.submitted, 
        CASE 
            WHEN A1.sid IN
                (SELECT A2.sid
                 FROM dbo.z_png_webform_cancelled_submissions) 
                THEN 'Cancelled'
            WHEN A1.is_draft=1
                THEN 'Draft'
            ELSE 'Submitted' 
        END AS status, 
        CASE 
            WHEN A1.sid IN (SELECT A2.sid FROM dbo.z_png_webform_cancelled_submissions) 
                THEN A2.cancelled_time
            ELSE '' 
        END AS cancelled_time
    FROM dbo.z_png_webform_cancelled_submissions AS A2 RIGHT OUTER JOIN
                                                  dbo.z_png_webform_submissions AS A1 ON A2.sid = A1.sid) AS B1 INNER JOIN
                           (SELECT        sid AS B2sid, MAX(CASE cid WHEN 29 THEN data ELSE '' END) AS country, MAX(CASE cid WHEN 12 THEN data ELSE '' END) 
                                                       AS employee_last_name, MAX(CASE cid WHEN 13 THEN data ELSE '' END) AS employee_first_name, MAX(CASE cid WHEN 30 THEN data ELSE '' END)
                                                        AS relocation_effective_date, MAX(CASE cid WHEN 24 THEN data ELSE '' END) AS relocation_type
                             FROM            dbo.z_png_webform_submitted_data
                             GROUP BY sid) AS B2 ON B1.sid = B2.B2sid